package cn.wangkai.peanut.db.util;

import java.sql.Date;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;

public class SQLUtil {

	private StringBuffer SQL;
	private List<Object> params;
	public SQLUtil() {
		params = new ArrayList<Object>();
		SQL = new StringBuffer();
	}

	public SQLUtil(String sql,List<Object> params) {
		if(params==null)
			this.params = new ArrayList<Object>();		
		else
			this.params = params;
		if(sql!=null) SQL = new StringBuffer(sql);
	}
	
	public SQLUtil(String sql,Object... params) {
		if(sql!=null) SQL = new StringBuffer(sql);
		this.params = new ArrayList<Object>();		
		if(params!=null){
			for (Object object : params) {
				addParams(object);
			}
		}
	}
	
	public SQLUtil(List<Object> params) {
		if(params==null)
			this.params = new ArrayList<Object>();		
		else
			this.params = params;
		SQL = new StringBuffer();
	}
	
	public StringBuffer getSqlBuffer() {
		return SQL;
	}
	public void setSqlBuffer(StringBuffer sql) {
		this.SQL = sql;
	}
	public void setSQL(String sql) {
		this.SQL.append(sql);
	}
	
	public List<Object> getParams() {
		return params;
	}

	public void addParams(Object param) {
		this.params.add(param);
	}
	
	public void setParams(List<Object> params) {
		this.params = params;
	}
	
	public void putlike(String keword,String value){
		if(StringUtils.isNotBlank(value)){
			SQL.append("AND "+keword+" like ? ");
			params.add("%"+value+"%");
		}
	}
	
	public void putleftlike(String keword,String value){
		if(StringUtils.isNotBlank(value)){
			SQL.append("AND "+keword+" like ? ");
			params.add("%"+value);
		}
	}
	
	public void putleftstrsplit(String keword,String splitstr,String value){
		if(StringUtils.isBlank(value)) return;
		if(value.indexOf(splitstr)<=0) {
			putleftlike(keword,value);
			return;
		}
		String s = null;
		SQL.append("AND (");
		for (String val : StringUtils.split(value, splitstr)) {
			if(s==null){
				s = "?";
				SQL.append(" "+keword+" like ? ");
				params.add("%"+val);
			}else{
				s += ",?";
				SQL.append("OR "+keword+" like ? ");
				params.add("%"+val);
			}
		}
		SQL.append(") ");
	}
	public void putrightstrsplit(String keword,String splitstr,String value){
		if(StringUtils.isBlank(value)) return;
		if(value.indexOf(splitstr)<=0) {
			putrightlike(keword,value);
			return;
		}
		String s = null;
		SQL.append("AND (");
		for (String val : StringUtils.split(value, splitstr)) {
			if(s==null){
				s = "?";
				SQL.append(" "+keword+" like ? ");
				params.add(val+"%");
			}else{
				s += ",?";
				SQL.append("OR "+keword+" like ? ");
				params.add(val+"%");
			}
		}
		SQL.append(") ");
	}
	
	public void putrightlike(String keword,String value){
		if(StringUtils.isNotBlank(value)){
			SQL.append("AND "+keword+" like ? ");
			params.add(value+"%");
		}
	}
	
	public void putstr(String keword,String value){
		if(StringUtils.isNotBlank(value)){
			SQL.append("AND "+keword+" = ? ");
			params.add(value);
		}
	}
	
	public void putNull(String keword,boolean isnull){
		if(isnull){
			SQL.append("AND "+keword+" is Null ");
		}else{
			SQL.append("AND "+keword+" is Not Null ");
		}
	}
	
	public void put(String keword,Object value){
		if(value!=null){
			SQL.append("AND "+keword+" = ? ");
			params.add(value);
		}
	}

	public void putNot(String keword,Object value){
		if(value!=null){
			SQL.append("AND "+keword+" <> ? ");
			params.add(value);
		}
	}

	
	public void putstrsplit(String keword,String splitstr,String value){
		if(StringUtils.isBlank(value)) return;
		if(value.indexOf(splitstr)<=0) {
			putstr(keword,value);
			return;
		}
		String[] vals = StringUtils.split(value, splitstr);
		String s = null;
		for (String val : vals) {
			if(s==null)
				s = "?";
			else
				s += ",?";
			params.add(val);
		}
		SQL.append("AND "+keword+" in ("+s+") ");
	}
	
	public void putIntegersplit(String keword,String splitstr,String value){
		if(StringUtils.isBlank(value)) return;
		if(value.indexOf(splitstr)<=0) {
			putstr(keword,value);
			return;
		}
		String[] vals = StringUtils.split(value, splitstr);
		String s = null;
		for (String val : vals) {
			if(s==null)
				s = "?";
			else
				s += ",?";
			params.add(NumberUtils.toInt(val));
		}
		SQL.append("AND "+keword+" in ("+s+") ");
	}
	
	public void putLongsplit(String keword,String splitstr,String value){
		if(StringUtils.isBlank(value)) return;
		if(value.indexOf(splitstr)<=0) {
			putstr(keword,value);
			return;
		}
		String[] vals = StringUtils.split(value, splitstr);
		String s = null;
		for (String val : vals) {
			if(s==null)
				s = "?";
			else
				s += ",?";
			params.add(NumberUtils.toLong(val));
		}
		SQL.append("AND "+keword+" in ("+s+") ");
	}
	
	public void putNotinstrsplit(String keword,String splitstr,String value){
		if(StringUtils.isBlank(value)) return;
		if(value.indexOf(splitstr)<=0) {
			putstr(keword,value);
			return;
		}
		String[] vals = StringUtils.split(value, splitstr);
		String s = null;
		for (String val : vals) {
			if(s==null)
				s = "?";
			else
				s += ",?";
			params.add(val);
		}
		SQL.append("AND "+keword+" in ("+s+") ");
	}
	
	public void putNotintegersplit(String keword,String splitstr,String value){
		if(StringUtils.isBlank(value)) return;
		if(value.indexOf(splitstr)<=0) {
			putstr(keword,value);
			return;
		}
		String[] vals = StringUtils.split(value, splitstr);
		String s = null;
		for (String val : vals) {
			if(s==null)
				s = "?";
			else
				s += ",?";
			params.add(NumberUtils.toInt(val));
		}
		SQL.append("AND "+keword+" not in ("+s+") ");
	}
	
	public void putNotinLongsplit(String keword,String splitstr,String value){
		if(StringUtils.isBlank(value)) return;
		if(value.indexOf(splitstr)<=0) {
			putstr(keword,value);
			return;
		}
		String[] vals = StringUtils.split(value, splitstr);
		String s = null;
		for (String val : vals) {
			if(s==null)
				s = "?";
			else
				s += ",?";
			params.add(NumberUtils.toLong(val));
		}
		SQL.append("AND "+keword+" not in ("+s+") ");
	}
	
	public static List<Object> putstrlist(String splitstr,String value){
		List<Object> lists = new ArrayList<Object>();
		if(StringUtils.isBlank(value)) return lists;
		if(value.indexOf(splitstr)<=0) {
			lists.add(value);
			return lists;
		}
		String[] vals = StringUtils.split(value, splitstr);
		for (String val : vals) {
			lists.add(val);
		}
		return lists;
	}
	
	public static List<Object> putstrslist(String[] vals){
		List<Object> lists = new ArrayList<Object>();
		for (String val : vals) {
			lists.add(val);
		}
		return lists;
	}
	
	public static String putlistsql(List<Object> lists){
		if(lists==null||lists.size()<=0) return "";
		String s = null;
		for (int i = 0; i < lists.size(); i++) {
			if(s==null)
				s = "?";
			else
				s += ",?";
		}
		return s;
	}
	
	/**
	 *  input putlistsql(5) retur ",?,?,?,?,?"
	 * @param j
	 * @return
	 */
	public static String putlistsql(Integer j){
		String s = null;
		for (int i = 0; i < j; i++) {
			if(s==null)
				s = "?";
			else
				s += ",?";
		}
		return s;
	}
	
	
	public void putDate(String keword,java.util.Date sdate,java.util.Date edate){
		putDate(keword, sdate==null?null:new Date(sdate.getTime()), edate==null?null:new Date(edate.getTime()));
	}
	
	public void putDate(String keword,Date sdate,Date edate){
		Timestamp edatetime = null;
		
		Timestamp sdatetime = null;
		if(sdate!=null){
			edatetime = new Timestamp(sdate.getTime());
		}
		if(edate!=null){
			sdatetime =new Timestamp(edate.getTime()+86399999);
		}
		putDatetime(keword, edatetime, sdatetime);
	}
	public void putDatetime(String keword,Timestamp sdate,Timestamp edate){
		if(sdate!=null&&edate!=null){
			SQL.append("AND "+keword+" between ? AND ? ");
			params.add(sdate);
			params.add(edate);
		}else if(sdate!=null){
			SQL.append("AND "+keword+">=? ");
			params.add(sdate);
		}else if(edate!=null){
			SQL.append("AND "+keword+"<=? ");
			params.add(edate);
		}
	}
	
	public static void main(String[] args) {

	}
	
	
	public String getSQL() {
		return SQL.toString();
	}
	
}
